package com.scorekeeper.utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBProvider {
	private static final String DATABASE_NAME = "keeper.db";
	private static final int DATABASE_VERSION = 1;
	private Context ctx = null;
	private SQLiteDatabase DBInstance;
	
	public static enum CastType {
		INT (1), STR (2), LONG (3), BOOL (4);

		private final int typeVal;

		private CastType(int _val) { 
			typeVal = _val; 
		}

		public final int toInt() { 
			return typeVal; 
		}

		public static CastType lookup(int typeVal) {
			for (CastType ct: CastType.values()) {
				if (typeVal == ct.toInt()) {
					return CastType.valueOf(ct.name());
				}
			}

			return null;
		}
	}

	public static enum Table {
		SEASON(0, "SEASON"),
		TEAM(1, "TEAM"),
		GAME(2, "GAME"),
		DIVISION(3, "DIVISION"),
		EXTENSION(4, "EXTENSION");

		private final int tblIndex;
		private final String tblName;

		private Table(int index, String tableName) { 
			tblIndex = index; 
			tblName = tableName;
		}

		final int Index() { 
			return tblIndex; 
		}

		public final String Name() {
			return tblName;
		}

	}
	
	private class OpenHelper extends SQLiteOpenHelper {
		public OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onOpen(SQLiteDatabase dbInstance) {
			dbInstance.setVersion(DATABASE_VERSION);
		}

		//Create all the tables
		@Override
		public void onCreate(SQLiteDatabase dbInstance) {

			dbInstance.execSQL("CREATE TABLE division (" +
					"Id INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT, " + 
					"DivisionName TEXT, " +
					"DivisionAcro TEXT);");

			dbInstance.execSQL("CREATE TABLE team ( " + 
					"Id      INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT, " + 
					"TeamName    TEXT    NOT NULL ON CONFLICT FAIL, " + 
					"Acro        TEXT    NOT NULL ON CONFLICT FAIL, " + 
					"DivisionId  INTEGER NOT NULL ON CONFLICT FAIL, " + 
					"GamesPlayed INTEGER DEFAULT ( 0 ), " + 
					"Wins        INTEGER DEFAULT ( 0 ), " + 
					"Losses      INTEGER DEFAULT ( 0 ), " + 
					"Points      INTEGER DEFAULT ( 0 ), " + 
					"FOREIGN KEY ( DivisionId ) REFERENCES Division ( DivisionId ));");

			dbInstance.execSQL("CREATE TABLE game ( " +
					"Id     INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT, " +
					"GameDate   NUMERIC, " +
					"HomeTeamId INTEGER NOT NULL ON CONFLICT FAIL, " +
					"AwayTeamId INTEGER NOT NULL ON CONFLICT FAIL, " +
					"HomeScore  INTEGER DEFAULT ( 0 ), " +
					"AwayScore  INTEGER DEFAULT ( 0 ), " +
					"SeasonId   INTEGER REFERENCES season ( SeasonId ), " +
					"FOREIGN KEY ( HomeTeamId, AwayTeamId ) REFERENCES team ( TeamId, TeamId ));");

			dbInstance.execSQL("CREATE TABLE season ( " +
					"Id INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT,  " +
					"SeasonName TEXT);");

			dbInstance.execSQL("CREATE TABLE ext_type ( " +
					"Id INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT, " +
					"Name TEXT, " +
					"Type TEXT);");

			dbInstance.execSQL("CREATE TABLE ext_value ( " +
					"Id INTEGER PRIMARY KEY ASC ON CONFLICT FAIL AUTOINCREMENT, " +
					"Name TEXT DEFAULT ( '' ), " +
					"Value TEXT DEFAULT ( '' ), " +
					"TeamId INTEGER, " +
					"ExtType INTEGER, " +
					"FOREIGN KEY ( ExtType ) REFERENCES ext_type ( Id ));");

			dbInstance.execSQL("CREATE TABLE updates ( " +
					"UpdatedTable TEXT PRIMARY KEY ASC ON CONFLICT FAIL, " +
					"UpdatedDate NUMERIC);");

			setupExtTypes(dbInstance);

			SQLiteDatabase.releaseMemory();
		}

		private void setupExtTypes(SQLiteDatabase dbInstance) {
			ContentValues cv = new ContentValues();

			cv.put("Name", CastType.INT.toString());
			cv.put("Type", CastType.INT.toInt());
			dbInstance.insert("ext_type", null, cv);
			cv.clear();

			cv.put("Name", CastType.STR.toString());
			cv.put("Type", CastType.STR.toInt());
			dbInstance.insert("ext_type", null, cv);
			cv.clear();

			cv.put("Name", CastType.BOOL.toString());
			cv.put("Type", CastType.BOOL.toInt());
			dbInstance.insert("ext_type", null, cv);
			cv.clear();

			cv.put("Name", CastType.LONG.toString());
			cv.put("Type", CastType.LONG.toInt());
			dbInstance.insert("ext_type", null, cv);
			cv.clear();
		}

		@Override
		public void onUpgrade(SQLiteDatabase dbInstance, int oldVersion, int newVersion) {
			//for now just drop and recreate!
			//TODO: A proper DB upgrade, involving from which version the upgrade is being 
			// performed from and which database version it's being upgraded too.
			switch (oldVersion) {
			case 1:
			default:
				dbInstance.execSQL("DROP TABLE IF EXISTS division");
				dbInstance.execSQL("DROP TABLE IF EXISTS team");
				dbInstance.execSQL("DROP TABLE IF EXISTS season");
				dbInstance.execSQL("DROP TABLE IF EXISTS ext_type");
				dbInstance.execSQL("DROP TABLE IF EXISTS game");
				dbInstance.execSQL("DROP TABLE IF EXISTS ext_value");
				dbInstance.execSQL("DROP TABLE IF EXISTS updates");

				onCreate(dbInstance);

				break;
			}

			SQLiteDatabase.releaseMemory();
		}
	}

	public DBProvider(Context context) {
		ctx = context;
	}

	//---opens the database---
	public DBProvider open() throws SQLException 
	{
		boolean getDatabase = false;
		
		try {
			OpenHelper oh = new OpenHelper(this.ctx);
			if (DBInstance == null) {
					getDatabase = true;
			} else {
				if (!DBInstance.isOpen()) {
					getDatabase = true;
				}
			}
			
			if (getDatabase) {
				DBInstance = oh.getWritableDatabase();
				DBInstance.setLockingEnabled(false);
			}
			
			return this;
		}
		catch (SQLiteException sqlE) {
			Log.e("DBProvider", "SQLLiteException", sqlE);
			return null;
		}
		catch (Exception ex) {
			Log.e("DBProvider", "SQLLiteException", ex);
			return null;
		}
	}
	
	//---closes the database---
	public void close() 
	{
		if (DBInstance != null) {
			if (DBInstance.isOpen()) {
				SQLiteDatabase.releaseMemory();
				DBInstance.close();
			}
		}
	}
	
	//---checks if the cursor is not null and moves to first record
	public boolean isValidCursor(Cursor cur) {
		if (cur != null) {
			if (cur.moveToFirst()) {
				return true;
			}
		}
		
		return false;
	}
	
	public int update(String tableName, ContentValues values, String whereClause, String... whereArgs) {
		return DBInstance.update(tableName, values, whereClause, whereArgs);
	}

	public long insert(String tableName, ContentValues values) {
		return DBInstance.insert(tableName, null, values);
	}
	
	public int delete(String tableName, String whereClause, String... whereArgs) {
		return DBInstance.delete(tableName, whereClause, whereArgs);
	}
	
	public Cursor select(String query, String[] queryArgs) {
		return DBInstance.rawQuery(query, queryArgs);
	}
}
